﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class ShipmentContainerDA : IShipmentContainerDA
    {
        private string BARCODE = "@BARCODE";
        private string CONTAINER_ID = "@CONTAINER_ID";
        private string CONTAINER_TYPE = "@CONTAINER_TYPE";
        private string CREATED_BY = "@CREATED_BY";
        private string EXTERN_LINE_ID = "@EXTERN_LINE_ID";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string HEIGHT = "@HEIGHT";
        private string INTERNAL_CONTAINER_ID = "@INTERNAL_CONTAINER_ID";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LENGTH = "@LENGTH";
        private string MULTI_FLAG = "@MULTI_FLAG";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_LINE_ID = "@ORDER_LINE_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PARENT_CONTAINER_ID = "@PARENT_CONTAINER_ID";
        private string QTY = "@QTY";
        private string REMARK = "@REMARK";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_SHIPMENT_CONTAINER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@SHIPMENT_CONTAINER WHERE CONTAINER_ID = @CONTAINER_ID AND ORDER_ID=@ORDER_ID ";
        private string SQL_DELETE_SHIPMENT_CONTAINER = " DELETE FROM @_@SHIPMENT_CONTAINER WHERE CONTAINER_ID = @CONTAINER_ID ";
        private string SQL_DELETE_SHIPMENT_CONTAINER_BY_INTERNAL_CONTAINER_ID = " DELETE FROM @_@SHIPMENT_CONTAINER WHERE INTERNAL_CONTAINER_ID = @INTERNAL_CONTAINER_ID ";
        private string SQL_INSERT_SHIPMENT_CONTAINER = " INSERT INTO @_@SHIPMENT_CONTAINER ( WH_ID, CONTAINER_ID, CONTAINER_TYPE, PARENT_CONTAINER_ID, MULTI_FLAG, STATUS, TOTAL_QTY, LENGTH, WIDTH, HEIGHT, VOLUME, WEIGHT, VALUE, WORKER_ID, ORDER_ID, ORDER_LINE_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, OWNER_ID, SKU_ID, QTY, UOM_ID, BARCODE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @CONTAINER_ID, @CONTAINER_TYPE, @PARENT_CONTAINER_ID, @MULTI_FLAG, @STATUS, @TOTAL_QTY, @LENGTH, @WIDTH, @HEIGHT, @VOLUME, @WEIGHT, @VALUE, @WORKER_ID, @ORDER_ID, @ORDER_LINE_ID, @EXTERN_ORDER_ID, @EXTERN_LINE_ID, @OWNER_ID, @SKU_ID, @QTY, @UOM_ID, @BARCODE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_SHIPMENT_CONTAINER = " SELECT  WH_ID, INTERNAL_CONTAINER_ID, CONTAINER_ID, CONTAINER_TYPE, PARENT_CONTAINER_ID, MULTI_FLAG, STATUS, TOTAL_QTY, LENGTH, WIDTH, HEIGHT, VOLUME, WEIGHT, VALUE, WORKER_ID, ORDER_ID, ORDER_LINE_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, OWNER_ID, SKU_ID, QTY, UOM_ID, BARCODE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@SHIPMENT_CONTAINER ";
        private string SQL_SELECT_ALL_SHIPMENT_CONTAINER_BY_ORDERID = "SELECT  ( CASE (SELECT COUNT(*) FROM @_@SHIPMENT_CONTAINER  WHERE ORDER_ID=@ORDER_ID AND IS_ACTIVE='Y' AND CONTAINER_ID=SC.CONTAINER_ID)WHEN '0' THEN'空箱' WHEN '1'  THEN (SELECT SKU_ID FROM @_@SHIPMENT_CONTAINER  WHERE ORDER_ID=@ORDER_ID AND IS_ACTIVE='Y'AND CONTAINER_ID=SC.CONTAINER_ID) ELSE '混箱' END ) AS SKU_ID, CONTAINER_ID, CONTAINER_TYPE, (SELECT SUM(QTY)FROM @_@SHIPMENT_CONTAINER WHERE ORDER_ID =@ORDER_ID AND IS_ACTIVE='Y'AND CONTAINER_ID=SC.CONTAINER_ID) AS TOTAL_QTY,  LENGTH, WIDTH,  HEIGHT,VOLUME, WEIGHT,(SELECT SUM(VALUE) FROM @_@SHIPMENT_CONTAINER WHERE ORDER_ID=@ORDER_ID AND IS_ACTIVE='Y'AND CONTAINER_ID=SC.CONTAINER_ID) AS VALUE,ORDER_ID,INTERNAL_CONTAINER_ID FROM @_@SHIPMENT_CONTAINER SC WHERE ORDER_ID =@ORDER_ID  AND IS_ACTIVE ='N' GROUP BY CONTAINER_ID,CONTAINER_TYPE, TOTAL_QTY,  LENGTH, WIDTH,  HEIGHT,VOLUME, WEIGHT, VALUE, ORDER_ID,SKU_ID,INTERNAL_CONTAINER_ID";
        private string SQL_UPDATE_SCAN_SHIPMENT_CONTAINER = " UPDATE @_@SHIPMENT_CONTAINER SET WH_ID = @WH_ID, CONTAINER_ID = @CONTAINER_ID, CONTAINER_TYPE = @CONTAINER_TYPE, PARENT_CONTAINER_ID = @PARENT_CONTAINER_ID, MULTI_FLAG = @MULTI_FLAG, STATUS = @STATUS, TOTAL_QTY = @TOTAL_QTY, LENGTH = @LENGTH, WIDTH = @WIDTH, HEIGHT = @HEIGHT, VOLUME = @VOLUME, WEIGHT = @WEIGHT, VALUE = @VALUE, WORKER_ID = @WORKER_ID, ORDER_ID = @ORDER_ID, ORDER_LINE_ID = @ORDER_LINE_ID, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, QTY = @QTY+QTY, UOM_ID = @UOM_ID, BARCODE = @BARCODE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE  CONTAINER_ID = @CONTAINER_ID AND ORDER_ID=@ORDER_ID AND SKU_ID=@SKU_ID AND ORDER_LINE_ID=@ORDER_LINE_ID   ";
        private string SQL_UPDATE_SHIPMENT_CONTAINER = " UPDATE @_@SHIPMENT_CONTAINER SET WH_ID = @WH_ID, CONTAINER_ID = @CONTAINER_ID, CONTAINER_TYPE = @CONTAINER_TYPE, PARENT_CONTAINER_ID = @PARENT_CONTAINER_ID, MULTI_FLAG = @MULTI_FLAG, STATUS = @STATUS, TOTAL_QTY = @TOTAL_QTY, LENGTH = @LENGTH, WIDTH = @WIDTH, HEIGHT = @HEIGHT, VOLUME = @VOLUME, WEIGHT = @WEIGHT, VALUE = @VALUE, WORKER_ID = @WORKER_ID, ORDER_ID = @ORDER_ID, ORDER_LINE_ID = @ORDER_LINE_ID, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, QTY = @QTY, UOM_ID = @UOM_ID, BARCODE = @BARCODE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE  CONTAINER_ID = @CONTAINER_ID AND ORDER_ID=@ORDER_ID AND SKU_ID=@SKU_ID AND ORDER_LINE_ID=@ORDER_LINE_ID ";
        private string SQL_UPDATE_SHIPMENT_CONTAINER_ENCASENESTING = "  UPDATE @_@SHIPMENT_CONTAINER SET PARENT_CONTAINER_ID = @PARENT_CONTAINER_ID WHERE   CONTAINER_ID = @CONTAINER_ID";
        private string STATUS = "@STATUS";
        private string TOTAL_QTY = "@TOTAL_QTY";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VALUE = "@VALUE";
        private string VOLUME = "@VOLUME";
        private string WEIGHT = "@WEIGHT";
        private string WH_ID = "@WH_ID";
        private string WIDTH = "@WIDTH";
        private string WORKER_ID = "@WORKER_ID";

        public bool CheckShipmentContainerIDUnique(string shipmentContainerID, string OrderID, string SkuID, string whLoginID)
        {
            string strSql = this.SQL_CHECK_SHIPMENT_CONTAINER_ID_UNIQUE.Replace("@_@", whLoginID) + "AND SKU_ID=@SKU_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", shipmentContainerID), new SqlParameter("@ORDER_ID", OrderID), new SqlParameter("@SKU_ID", SkuID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, strSql, paras).ToString() == "1");
        }

        public bool CheckShipmentContainerIDUnique(DataBase dataBase, DbTransaction tran, string shipmentContainerID, string OrderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", shipmentContainerID), new SqlParameter("@ORDER_ID", OrderID) };
            return (Convert.ToInt32(dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_SHIPMENT_CONTAINER_ID_UNIQUE.Replace("@_@", whLoginID), paras)) > 0);
        }

        public bool CheckShipmentContainerIDUnique(DataBase dataBase, DbTransaction tran, string shipmentContainerID, string OrderID, string SkuID, string whLoginID)
        {
            string strSql = this.SQL_CHECK_SHIPMENT_CONTAINER_ID_UNIQUE.Replace("@_@", whLoginID) + "AND SKU_ID=@SKU_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", shipmentContainerID), new SqlParameter("@ORDER_ID", OrderID), new SqlParameter("@SKU_ID", SkuID) };
            return (Convert.ToInt32(dataBase.ExecuteScalar(tran, CommandType.Text, strSql, paras)) > 0);
        }

        public bool CheckShipmentContainerIDUnique(DataBase dataBase, DbTransaction tran, string shipmentContainerID, string OrderID, string SkuID, int OrderLineID, string whLoginID)
        {
            string strSql = this.SQL_CHECK_SHIPMENT_CONTAINER_ID_UNIQUE.Replace("@_@", whLoginID) + "AND SKU_ID=@SKU_ID AND ORDER_LINE_ID=@ORDER_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", shipmentContainerID), new SqlParameter("@ORDER_ID", OrderID), new SqlParameter("@SKU_ID", SkuID), new SqlParameter("@ORDER_LINE_ID", OrderLineID) };
            return (Convert.ToInt32(dataBase.ExecuteScalar(tran, CommandType.Text, strSql, paras)) > 0);
        }

        public int DeleteShipmentContainer(List<string> shipmentContainerID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < shipmentContainerID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTERNAL_CONTAINER_ID", shipmentContainerID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SHIPMENT_CONTAINER_BY_INTERNAL_CONTAINER_ID.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteShipmentContainer(DataBase dataBase, DbTransaction tran, List<string> shipmentContainerID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < shipmentContainerID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INTERNAL_CONTAINER_ID", shipmentContainerID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SHIPMENT_CONTAINER_BY_INTERNAL_CONTAINER_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteShipmentContainerByOrderLineID(DataBase dataBase, DbTransaction tran, string ContainerID, string OrderID, string SkuID, int OrderLineID, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_DELETE_SHIPMENT_CONTAINER.Replace("@_@", whLoginID) + " AND ORDER_ID=@ORDER_ID AND SKU_ID=@SKU_ID AND ORDER_LINE_ID=@ORDER_LINE_ID";
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", ContainerID), new SqlParameter("@ORDER_ID", OrderID), new SqlParameter("@SKU_ID", SkuID), new SqlParameter("@ORDER_LINE_ID", OrderLineID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int EncaseNesting(string strFromContainerID, string strToContainerID, string whLoginID)
        {
            string strSql = this.SQL_UPDATE_SHIPMENT_CONTAINER_ENCASENESTING.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", strFromContainerID), new SqlParameter("@PARENT_CONTAINER_ID", strToContainerID) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, strSql, paras);
        }

        public DataSet GetAllShipmentContainer(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SHIPMENT_CONTAINER.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllShipmentContainer(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SHIPMENT_CONTAINER.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public ShipmentContainerInfo GetShipmentContainerByID(string shipmentcontainerID, string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SHIPMENT_CONTAINER.Replace("@_@", whLoginID) + " WHERE CONTAINER_ID = @CONTAINER_ID AND ORDER_ID=@ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", shipmentcontainerID), new SqlParameter("@ORDER_ID", orderID) };
            ShipmentContainerInfo shipmentContainerInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    shipmentContainerInfo = new ShipmentContainerInfo(reader["WH_ID"].ToString(), (reader["INTERNAL_CONTAINER_ID"].ToString() != "") ? decimal.Parse(reader["INTERNAL_CONTAINER_ID"].ToString()) : 0M, reader["CONTAINER_ID"].ToString(), reader["CONTAINER_TYPE"].ToString(), reader["PARENT_CONTAINER_ID"].ToString(), reader["MULTI_FLAG"].ToString(), reader["STATUS"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["LENGTH"].ToString() != "") ? ((double) float.Parse(reader["LENGTH"].ToString())) : ((double) 0f), (reader["WIDTH"].ToString() != "") ? ((double) float.Parse(reader["WIDTH"].ToString())) : ((double) 0f), (reader["HEIGHT"].ToString() != "") ? ((double) float.Parse(reader["HEIGHT"].ToString())) : ((double) 0f), (reader["VOLUME"].ToString() != "") ? ((double) float.Parse(reader["VOLUME"].ToString())) : ((double) 0f), (reader["WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["WEIGHT"].ToString())) : ((double) 0f), (reader["VALUE"].ToString() != "") ? ((double) float.Parse(reader["VALUE"].ToString())) : ((double) 0f), reader["WORKER_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["BARCODE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return shipmentContainerInfo;
        }

        public ShipmentContainerInfo GetShipmentContainerByID(DataBase dataBase, DbTransaction tran, string shipmentcontainerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SHIPMENT_CONTAINER.Replace("@_@", whLoginID) + " WHERE CONTAINER_ID = @CONTAINER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", shipmentcontainerID) };
            ShipmentContainerInfo shipmentContainerInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                shipmentContainerInfo = new ShipmentContainerInfo(reader["WH_ID"].ToString(), (reader["INTERNAL_CONTAINER_ID"].ToString() != "") ? decimal.Parse(reader["INTERNAL_CONTAINER_ID"].ToString()) : 0M, reader["CONTAINER_ID"].ToString(), reader["CONTAINER_TYPE"].ToString(), reader["PARENT_CONTAINER_ID"].ToString(), reader["MULTI_FLAG"].ToString(), reader["STATUS"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["LENGTH"].ToString() != "") ? ((double) float.Parse(reader["LENGTH"].ToString())) : ((double) 0f), (reader["WIDTH"].ToString() != "") ? ((double) float.Parse(reader["WIDTH"].ToString())) : ((double) 0f), (reader["HEIGHT"].ToString() != "") ? ((double) float.Parse(reader["HEIGHT"].ToString())) : ((double) 0f), (reader["VOLUME"].ToString() != "") ? ((double) float.Parse(reader["VOLUME"].ToString())) : ((double) 0f), (reader["WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["WEIGHT"].ToString())) : ((double) 0f), (reader["VALUE"].ToString() != "") ? ((double) float.Parse(reader["VALUE"].ToString())) : ((double) 0f), reader["WORKER_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["BARCODE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return shipmentContainerInfo;
        }

        public List<ShipmentContainerInfo> GetShipmentContainerByOrderID(string OrderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SHIPMENT_CONTAINER_BY_ORDERID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", OrderID) };
            List<ShipmentContainerInfo> shipmentContainerInfoList = new List<ShipmentContainerInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    ShipmentContainerInfo shipmentContainerInfo = new ShipmentContainerInfo(reader["CONTAINER_ID"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["LENGTH"].ToString() != "") ? ((double) float.Parse(reader["LENGTH"].ToString())) : ((double) 0f), (reader["WIDTH"].ToString() != "") ? ((double) float.Parse(reader["WIDTH"].ToString())) : ((double) 0f), (reader["HEIGHT"].ToString() != "") ? ((double) float.Parse(reader["HEIGHT"].ToString())) : ((double) 0f), (reader["VOLUME"].ToString() != "") ? ((double) float.Parse(reader["VOLUME"].ToString())) : ((double) 0f), (reader["WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["WEIGHT"].ToString())) : ((double) 0f), (reader["VALUE"].ToString() != "") ? ((double) float.Parse(reader["VALUE"].ToString())) : ((double) 0f), reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["INTERNAL_CONTAINER_ID"].ToString() != "") ? decimal.Parse(reader["INTERNAL_CONTAINER_ID"].ToString()) : 0M);
                    shipmentContainerInfoList.Add(shipmentContainerInfo);
                }
            }
            return shipmentContainerInfoList;
        }

        public List<ShipmentContainerInfo> GetShipmentContainerListByOrderID(string OrderID, string ContainerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_SHIPMENT_CONTAINER.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID AND CONTAINER_ID=@CONTAINER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", OrderID), new SqlParameter("@CONTAINER_ID", ContainerID) };
            List<ShipmentContainerInfo> shipmentContainerInfoList = new List<ShipmentContainerInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    ShipmentContainerInfo shipmentContainerInfo = new ShipmentContainerInfo(reader["WH_ID"].ToString(), (reader["INTERNAL_CONTAINER_ID"].ToString() != "") ? decimal.Parse(reader["INTERNAL_CONTAINER_ID"].ToString()) : 0M, reader["CONTAINER_ID"].ToString(), reader["CONTAINER_TYPE"].ToString(), reader["PARENT_CONTAINER_ID"].ToString(), reader["MULTI_FLAG"].ToString(), reader["STATUS"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["LENGTH"].ToString() != "") ? ((double) float.Parse(reader["LENGTH"].ToString())) : ((double) 0f), (reader["WIDTH"].ToString() != "") ? ((double) float.Parse(reader["WIDTH"].ToString())) : ((double) 0f), (reader["HEIGHT"].ToString() != "") ? ((double) float.Parse(reader["HEIGHT"].ToString())) : ((double) 0f), (reader["VOLUME"].ToString() != "") ? ((double) float.Parse(reader["VOLUME"].ToString())) : ((double) 0f), (reader["WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["WEIGHT"].ToString())) : ((double) 0f), (reader["VALUE"].ToString() != "") ? ((double) float.Parse(reader["VALUE"].ToString())) : ((double) 0f), reader["WORKER_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["BARCODE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    shipmentContainerInfoList.Add(shipmentContainerInfo);
                }
            }
            return shipmentContainerInfoList;
        }

        public int InsertShipmentContainer(ShipmentContainerInfo shipmentContainerInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                shipmentContainerInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ShipmentContainer_Parameters(shipmentContainerInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SHIPMENT_CONTAINER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertShipmentContainer(DataBase dataBase, DbTransaction tran, ShipmentContainerInfo shipmentContainerInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                shipmentContainerInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ShipmentContainer_Parameters(shipmentContainerInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SHIPMENT_CONTAINER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int ScanUpdateShipmentContainer(DataBase dataBase, DbTransaction tran, ShipmentContainerInfo shipmentContainerInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                shipmentContainerInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ShipmentContainer_Parameters(shipmentContainerInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SCAN_SHIPMENT_CONTAINER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_ShipmentContainer_Parameters(ShipmentContainerInfo shipmentContainerInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.INTERNAL_CONTAINER_ID, SqlDbType.Decimal, 5), new SqlParameter(this.CONTAINER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.PARENT_CONTAINER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.MULTI_FLAG, SqlDbType.VarChar, 1), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.TOTAL_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LENGTH, SqlDbType.Float, 8), new SqlParameter(this.WIDTH, SqlDbType.Float, 8), new SqlParameter(this.HEIGHT, SqlDbType.Float, 8), new SqlParameter(this.VOLUME, SqlDbType.Float, 8), new SqlParameter(this.WEIGHT, SqlDbType.Float, 8), new SqlParameter(this.VALUE, SqlDbType.Float, 8), new SqlParameter(this.WORKER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.ORDER_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERN_LINE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.BARCODE, SqlDbType.VarChar, 30), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x200), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(shipmentContainerInfo.WhID))
            {
                paramArray[0].Value = shipmentContainerInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = shipmentContainerInfo.InternalContainerID;
            paramArray[2].Value = shipmentContainerInfo.ContainerID;
            paramArray[3].Value = shipmentContainerInfo.ContainerType;
            if (!string.IsNullOrEmpty(shipmentContainerInfo.ParentContainerID))
            {
                paramArray[4].Value = shipmentContainerInfo.ParentContainerID;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = shipmentContainerInfo.MultiFlag;
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Status))
            {
                paramArray[6].Value = shipmentContainerInfo.Status;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            paramArray[7].Value = shipmentContainerInfo.TotalQty;
            paramArray[8].Value = shipmentContainerInfo.Length;
            paramArray[9].Value = shipmentContainerInfo.Width;
            paramArray[10].Value = shipmentContainerInfo.Height;
            paramArray[11].Value = shipmentContainerInfo.Volume;
            paramArray[12].Value = shipmentContainerInfo.Weight;
            paramArray[13].Value = shipmentContainerInfo.Value;
            if (!string.IsNullOrEmpty(shipmentContainerInfo.WorkerID))
            {
                paramArray[14].Value = shipmentContainerInfo.WorkerID;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.OrderID))
            {
                paramArray[15].Value = shipmentContainerInfo.OrderID;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            paramArray[0x10].Value = shipmentContainerInfo.OrderLineID;
            paramArray[0x11].Value = shipmentContainerInfo.ExternOrderID;
            paramArray[0x12].Value = shipmentContainerInfo.ExternLineID;
            paramArray[0x13].Value = shipmentContainerInfo.OwnerID;
            paramArray[20].Value = shipmentContainerInfo.SkuID;
            paramArray[0x15].Value = shipmentContainerInfo.Qty;
            paramArray[0x16].Value = shipmentContainerInfo.UomID;
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Barcode))
            {
                paramArray[0x17].Value = shipmentContainerInfo.Barcode;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf1))
            {
                paramArray[0x18].Value = shipmentContainerInfo.Udf1;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf2))
            {
                paramArray[0x19].Value = shipmentContainerInfo.Udf2;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf3))
            {
                paramArray[0x1a].Value = shipmentContainerInfo.Udf3;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf4))
            {
                paramArray[0x1b].Value = shipmentContainerInfo.Udf4;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf5))
            {
                paramArray[0x1c].Value = shipmentContainerInfo.Udf5;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf6))
            {
                paramArray[0x1d].Value = shipmentContainerInfo.Udf6;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf7))
            {
                paramArray[30].Value = shipmentContainerInfo.Udf7;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf8))
            {
                paramArray[0x1f].Value = shipmentContainerInfo.Udf8;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf9))
            {
                paramArray[0x20].Value = shipmentContainerInfo.Udf9;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Udf10))
            {
                paramArray[0x21].Value = shipmentContainerInfo.Udf10;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(shipmentContainerInfo.Remark))
            {
                paramArray[0x22].Value = shipmentContainerInfo.Remark;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            paramArray[0x23].Value = shipmentContainerInfo.IsActive;
            paramArray[0x24].Value = shipmentContainerInfo.CreatedBy;
            paramArray[0x25].Value = shipmentContainerInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateShipmentContainer(ShipmentContainerInfo shipmentContainerInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                shipmentContainerInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ShipmentContainer_Parameters(shipmentContainerInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SHIPMENT_CONTAINER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateShipmentContainer(DataBase dataBase, DbTransaction tran, ShipmentContainerInfo shipmentContainerInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                shipmentContainerInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ShipmentContainer_Parameters(shipmentContainerInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SHIPMENT_CONTAINER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

